﻿using Coldairarrow.Business.AOP;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.FP;
using Coldairarrow.Entity.PB;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_DeliveryPlanBusiness : BaseBusiness<PB_DeliveryPlan>, IPB_DeliveryPlanBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly IOperatorDept _odept;
        public PB_DeliveryPlanBusiness(IDbAccessor db, IOperator @operator, IOperatorDept @odept)
            : base(db)
        {
            _odept = @odept;
            _operator = @operator;
        }

        #region 外部接口

        public async Task<PageResult<PB_DeliveryPlan>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_DeliveryPlan>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_DeliveryPlan, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_DeliveryPlan> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_DeliveryPlan data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_DeliveryPlan data)
        {
            await UpdateAsync(data);
        }
        [OverFlowDelete]
        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }

        /// <summary>
        /// 保存发货计划明细信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        //[OverFlowBegin("DeliveryPlan")]
        public async Task AddDataList(SavaDataDeliveryPlan data)
        {
            //创建主数据
            //String Pid = IdHelper.GetId();
            List<PB_DeliveryPlanDetails> SubData = new List<PB_DeliveryPlanDetails>();
            foreach (var item in data.SubData)
            {
                string Subid = IdHelper.GetId();
                PB_DeliveryPlanDetails pB_DeliveryPlanDetails1 = new PB_DeliveryPlanDetails();
                pB_DeliveryPlanDetails1.Id = Subid;
                pB_DeliveryPlanDetails1.ProjectId = item.ProjectId;
                pB_DeliveryPlanDetails1.BuildingId = item.BuildingId;
                pB_DeliveryPlanDetails1.TrainNumber = item.TrainNumber;
                pB_DeliveryPlanDetails1.ShipmentQuantity = item.ShipmentQuantity;
                pB_DeliveryPlanDetails1.ShipmentWight = item.ShipmentWight;
                pB_DeliveryPlanDetails1.ActualQuantity = 0;
                pB_DeliveryPlanDetails1.ActualWight = 0;
                pB_DeliveryPlanDetails1.ParentId = data.Id;
                pB_DeliveryPlanDetails1.CreateTime = DateTime.Now;
                pB_DeliveryPlanDetails1.Louhao = item.Louhao;
                pB_DeliveryPlanDetails1.CreatorId = _operator.UserId;
                pB_DeliveryPlanDetails1.Deleted = false;
                pB_DeliveryPlanDetails1.Message = item.Message;
                pB_DeliveryPlanDetails1.QrCode = base_RepairOrderDetailsCode + "," + Subid;
                pB_DeliveryPlanDetails1.States = 0;//发货状态 0已制单  1已创建设计清单  2已创建发货通知单  3已创建备货单    4。备货完成
                                                   //var WeightRemoval =await Db.GetIQueryable<PB_DeliveryPlanDetails>().Where(m=>m.BuildingId== item.BuildingId).FirstOrDefaultAsync();
                                                   //if (WeightRemoval!=null) {
                                                   //    var isnus =await Db.GetIQueryable<PB_Buildingnumber>().Where(m=>m.Id== WeightRemoval.BuildingId).FirstOrDefaultAsync();
                                                   //var project=await Db.GetIQueryable<PB_Project>().Where()

                //    throw new BusException($"该楼号已有发货计划{isnus.Buildingnumber}");
                //}
                SubData.Add(pB_DeliveryPlanDetails1);

            }
            PB_DeliveryPlan pB_DeliveryPlan = new PB_DeliveryPlan
            {
                Id = data.Id,//编号
                DeptId = data.DeptId,
                PlannedDate = data.PlannedDate,
                Closingdate = data.Closingdate,
                Remarks = data.Remarks,
                CreateTime = DateTime.Now,
                Deleted = false,
                States = 0,
                ToExamineId = "",
                DepartmentId = data.DepartmentId,
                QrCode = data.Id,
                CreatorId = _operator.UserId,
                WorkFlow = data.WorkFlow,
                IsRepeat = data.IsRepeat,
            };
            //插入工作流日志
            await Db.InsertAsync<PB_DeliveryPlanDetails>(SubData);
            await Db.InsertAsync<PB_DeliveryPlan>(pB_DeliveryPlan);
            //判断工作流不为空 插入工作流数据
            //String OverFlowLogId = IdHelper.GetId();
            //Base_OverFlowLog OverFlowLog = new Base_OverFlowLog();
            //OverFlowLog.Id = OverFlowLogId;
            //OverFlowLog.CreatorId = _operator.UserId;
            //OverFlowLog.CreateTime = DateTime.Now;
            //OverFlowLog.OverFlowId = data.WorkFlow;
            //OverFlowLog.OverFlowDetailID = "";
            //OverFlowLog.BillType = "";
            //OverFlowLog.BillId = Pid;
            //OverFlowLog.AssignTo = data.DepartmentId;
            //List<PB_PickUpPlanList> flowProcessItems = new List<PB_PickUpPlanList>();
            //foreach (var item in SubData)
            //{
            //    string SQL = "select DATE_FORMAT(a.PlannedDate, '%Y-%m-%d') PlannedDate,DATE_FORMAT(a.Closingdate, '%Y-%m-%d') Closingdate,c.Name,d.Buildingnumber,b.TrainNumber,b.ShipmentQuantity,b.ShipmentWight,b.ActualQuantity,b.ActualWight,b.Message,b.Id AS aId from PB_DeliveryPlan a INNER JOIN PB_DeliveryPlanDetails b on a.Id = b.ParentId INNER JOIN PB_Project c on b.ProjectId = c.Id INNER JOIN PB_Buildingnumber d on b.BuildingId = d.Id INNER JOIN Base_Registration as e on a.DepartmentId = e.DepartmentId where b.Id =  '" + item.Id + "'";
            //    List<PB_PickUpPlanList> list = await Db.GetListBySqlAsync<PB_PickUpPlanList>(SQL);
            //    foreach (var items in list)
            //    {
            //        flowProcessItems.Add(items);
            //    }
            //}
            //OverFlowLog.Remark = flowProcessItems.ToJson();
            //await Db.InsertAsync<Base_OverFlowLog>(OverFlowLog);


        }
        /// <summary>
        /// 编辑操作
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpdateDataList(SavaDataDeliveryPlan data)
        {
            //删除明细信息
            await ExecuteSqlAsync(" delete from PB_DeliveryPlanDetails where ParentId='" + data.Id + "'");
            var addDetail = data.SubData.Select(x => new PB_DeliveryPlanDetails
            {
                Id = IdHelper.GetId(),//id
                ParentId = data.Id,//主表信息
                ProjectId = x.ProjectId,//物料编号
                BuildingId = x.BuildingId,
                CreateTime = DateTime.Now,
                CreatorId = _operator.UserId,
                TrainNumber = x.TrainNumber,
                ShipmentQuantity = x.ShipmentQuantity,
                ShipmentWight = x.ShipmentWight,
                ActualQuantity = 0,
                Louhao = x.Louhao,
                ActualWight = 0,
                Message = x.Message,
                QrCode = x.Id,
                Deleted = false,
                States = 0,//发货状态 0已制单  1已创建设计清单  2已创建发货通知单  3已创建备货单    4。备货完成
            }).ToList();
            foreach (var item in addDetail)
            {
                item.QrCode = base_RepairOrderDetailsCode + "," + item.Id;
            }
            await Db.InsertAsync<PB_DeliveryPlanDetails>(addDetail);
        }

        /// <summary>
        /// pc 发货计划查看列表
        /// </summary>
        /// <param name="ParentId"></param>
        /// <returns></returns>
        public async Task<DeliveryPlanListInfo> GetDeliveryPlanListInfo(string ParentId)
        {
            Expression<Func<PB_DeliveryPlan, Base_Department, Base_Department, Base_User, Base_OverFlow, DeliveryPlanListInfo>> select = (a, b, c, d, e) => new DeliveryPlanListInfo
            {
                DeptName = b.Name,
                DepartmentName = c.Name,
                CreatorId = d.RealName,
                WorkFlowName = e.Title,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.DepartmentId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.CreatorId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_OverFlow>() on a.WorkFlow equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            var main = await q.Where(m => m.Id == ParentId).FirstOrDefaultAsync();
            Expression<Func<PB_DeliveryPlanDetails, PB_Project, PB_Buildingnumber, DeliveryPlanDetailsDTO>> selects = (a, b, c) => new DeliveryPlanDetailsDTO
            {
                ProjectName = b.Name,
                BuildingId = c.Id
            };
            selects = selects.BuildExtendSelectExpre();
            var k = from a in Db.GetIQueryable<PB_DeliveryPlanDetails>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Project>() on a.ProjectId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Buildingnumber>() on a.BuildingId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @selects.Invoke(a, b, c);
            k = k.Where(x => x.ParentId == ParentId);
            var res = await k.ToListAsync();

            DeliveryPlanListInfo data = new DeliveryPlanListInfo();
            data.Id = main.Id;
            data.DeptId = main.DeptId;
            data.DepartmentId = main.DepartmentId;
            data.DeptName = main.DeptName;
            data.DepartmentName = main.DepartmentName;
            data.WorkFlowName = main.WorkFlowName;
            data.CreatorId = main.CreatorId;
            data.PlannedDate = main.PlannedDate;
            data.Closingdate = main.Closingdate;
            data.Remarks = main.Remarks;
            data.CreateTime = main.CreateTime;

            data.SubData = res;

            return data;
        }

        /// <summary>
        /// 递归查找公司下所有的仓库
        /// </summary>
        /// <param name="deptId">组织Id</param>
        /// <param name="deptlist">是仓库的集合</param>
        /// <param name="Departmentdata">组织数据</param>
        /// <returns></returns>
        private List<string> GetDeptItem(string deptId, List<string> deptlist, List<Base_Department> Departmentdata)
        {
            var dlist = Departmentdata.Where(x => x.ParentId == deptId).ToList();
            foreach (var item in dlist)
            {
                deptlist.Add(item.Id);
                GetDeptItem(item.Id, deptlist, Departmentdata);
            }
            return deptlist;
        }
        /// <summary>
        /// 审核发货计划
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task ToExamineOk(string Id)
        {
            string sqlMain = @" update PB_DeliveryPlan set `States`=1,ToExamineId='" + _operator.UserId + "'  where Id='" + Id + "' ";
            await ExecuteSqlAsync(sqlMain);
        }
        /// <summary>
        /// 获取发货计划列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_DeliveryPlanList>> GetDataListInfo(PageInput<PB_ScheduleDetailConditionDTO> input)
        {
            Expression<Func<PB_DeliveryPlan, Base_Department, Base_Department, Base_User, Base_User, PB_DeliveryPlanList>> select = (a, b, c, d, e) => new PB_DeliveryPlanList
            {
                DeptIdName = b.Name,
                DepartmentIdName = c.Name,
                CreatorIdName = d.RealName,
                ToExamineIdName = e.RealName,
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.DepartmentId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.CreatorId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_User>() on a.ToExamineId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            if (!input.Search.Name.IsNullOrEmpty())
            {
                q = q.Where(x => x.CreatorIdName.Contains(input.Search.Name));
            }
            if (!search.StartDate.IsNullOrEmpty() && !search.EndDate.IsNullOrEmpty())
            {
                DateTime StartDate = Convert.ToDateTime(Convert.ToDateTime(search.StartDate).Date.ToString());
                DateTime EndDate = Convert.ToDateTime(Convert.ToDateTime(search.EndDate).Date.ToString()).AddDays(1).AddSeconds(-1);
                q = q.Where(x => StartDate <= x.PlannedDate && x.Closingdate <= EndDate);
            }
            if (!input.Search.Keyword.IsNullOrEmpty())
            {
                //子表数据
                Expression<Func<PB_DeliveryPlanDetails, PB_Project, PB_DeliveryPlanDetailsDTO>> selectk = (a, b) => new PB_DeliveryPlanDetailsDTO
                {

                    ProjectName = b.Name,
                    ProjectId = b.Id,

                };
                selectk = selectk.BuildExtendSelectExpre();
                var k = from a in Db.GetIQueryable<PB_DeliveryPlanDetails>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Project>() on a.ProjectId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()

                        select @selectk.Invoke(a, b);

                var res = await k.Where(x => x.ProjectName.Contains(search.Keyword)).ToListAsync();

                List<String> names = new List<string>();

                foreach (var item in res)
                {
                    names.Add(item.ParentId);
                }
                q = q.Where(x => names.Contains(x.Id));
            }
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DepartmentId));
            var AA = await q.GetPageResultAsync(input);
            return AA;
        }


        public async Task<List<Base_Department>> GetDetpListAsync()
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            var deptdatas = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == userdata.DepartmentId).FirstOrDefaultAsync();
            var detpdata = await _odept.GetOperatorDeptGongchang();
            List<Base_Department> Departmentdata = await Db.GetIQueryable<Base_Department>().ToListAsync();
            var MainData = Departmentdata.Where(x => x.Type == 7).ToList();
            List<Base_Department> Data = new List<Base_Department>();
            foreach (var a in MainData)
            {
                if (deptdatas.Type == 1)
                {
                    Base_Department deptlist2 = Departmentdata.Where(x => x.Id == a.Id).FirstOrDefault();
                    Data.Add(deptlist2);
                }
                else
                {
                    Base_Department deptlist3 = Departmentdata.Where(x => x.Id == a.Id && userdata.FactoryId == a.Id && x.Type == 7).FirstOrDefault();
                    if (deptlist3 == null)
                    {
                        Data.Remove(deptlist3);
                    }
                    Data.Add(deptlist3);
                }
            }
            List<string> liststr = new List<string>();
            foreach (var item in Data)
            {
                if (item != null)
                {
                    liststr.Add(item.Id);
                    GetDeptItem(item.Id, liststr, Departmentdata);
                }

            }
            List<Base_Department> listdata = Departmentdata.Where(x => liststr.Contains(x.Id) && x.Type == 7).ToList();
            return listdata;
        }

        /// <summary>
        /// 大屏发货进度
        /// </summary>
        /// <returns>{"label": "发货进度","value": 43.12,"data": 43.12}</returns>
        public PB_DeliveryPlanProgressDTO GetTodayProgressAsync(int toTodayDays = 0)
        {
            string strSQL = @"SELECT '发货进度百分比' AS label, 
IF(SUM(a.ShipmentWight) IS NULL OR SUM(a.ShipmentWight)=0,0,IF(SUM(b.PackWeight)>SUM(a.ShipmentWight),100,IFNULL(SUM(b.PackWeight),0)/SUM(a.ShipmentWight)*100)) AS value,
IF(SUM(a.ShipmentWight) IS NULL OR SUM(a.ShipmentWight)=0,0,IF(SUM(b.PackWeight)>SUM(a.ShipmentWight),100,IFNULL(SUM(b.PackWeight),0)/SUM(a.ShipmentWight)*100)) AS data 
FROM
(SELECT dp.Id,SUM(dd.ShipmentWight) AS ShipmentWight FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays.ToString() + @" AND dd.ParentId = dp.Id AND dp.States IN(1,2) GROUP BY dp.Id) a
LEFT JOIN
(SELECT dp.Id,SUM(td.PackNums*m.Weight) AS PackWeight FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays.ToString() + @" AND dd.ParentId = dp.Id
JOIN PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID 
JOIN PB_DesignDetail dt ON dt.ParentId=dl.Id
JOIN PB_TransportBillDetail td ON td.DesignDetailId=dt.Id
JOIN PB_TransportBill tb ON td.ParentId=tb.Id AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=" + toTodayDays.ToString() + @"
JOIN PB_Material m ON td.MaterialId=m.Id
GROUP BY dp.Id) b
ON a.Id=b.Id";
            var res = Db.GetListBySqlAsync<PB_DeliveryPlanProgressDTO>(strSQL).Result.FirstOrDefault();

            if (res != null)
            {
                //四舍五入保留两位小数
                res.value = Convert.ToDecimal(res.value.ToString("#0.##"));
                res.data = res.value;
            }
            return res;
        }
        /// <summary>
        /// 大屏显示当日待发货项目数量
        /// </summary>
        /// <returns>{"value": "100"}</returns>
        public PB_DeliveryPlanValueDTO GetTodayReadyDeliveryProject(int toTodayDays = 0)
        {
            //求已发货项目总数;
            string strSQL = @"SELECT COUNT(DISTINCT dd.ProjectId) AS value FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays.ToString() + @" AND dd.ParentId = dp.Id
JOIN  PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID
JOIN PB_DesignDetail dt ON dt.ParentId=dl.Id
JOIN PB_TransportBillDetail td ON td.DesignDetailId=dt.Id
JOIN PB_TransportBill tb ON tb.Id = td.ParentId AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=" + toTodayDays.ToString();
            int done = Db.GetListBySqlAsync<PB_DeliveryPlanValue2DTO>(strSQL).Result.FirstOrDefault().value;
            //求项目总数
            strSQL = @"SELECT COUNT(DISTINCT dd.ProjectId) AS value FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays + @" AND dd.ParentId = dp.Id";
            int total = Db.GetListBySqlAsync<PB_DeliveryPlanValue2DTO>(strSQL).Result.FirstOrDefault().value;
            PB_DeliveryPlanValueDTO res = new PB_DeliveryPlanValueDTO { value = (total - done).ToString() };
            if (res != null)
            {
                return res;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }
        /// <summary>
        /// 大屏显示当日未备货数量
        /// </summary>
        /// <returns></returns>
        public PB_DeliveryPlanValue3DTO GetTodayNoPickUpAmount(int toTodayDays = 0)
        {
            string strSQL = @"SELECT IFNULL(SUM(dd.ShipmentQuantity),0) AS value FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays;
            var total = Db.GetListBySqlAsync<PB_DeliveryPlanValue3DTO>(strSQL).Result.FirstOrDefault();
            strSQL = @"SELECT IFNULL(SUM(pl.ReadyNums),0) AS value	FROM PB_DeliveryPlanDetails dd";
            strSQL += " JOIN  PB_DeliveryPlan dp ON dp.States IN (1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays + @" AND dd.ParentId=dp.Id";
            strSQL += " JOIN  PB_DesignList  dl ON dd.ProjectId=dl.ProjectId AND dd.BuildingId=dl.BuildingNumberID";
            strSQL += " JOIN  PB_DeliveryNoticeItem dni ON dl.Id=dni.DesignId";
            strSQL += " JOIN  PB_PickUpMain pm ON pm.ParentId=dni.Id AND pm.`Status`=3";
            strSQL += " JOIN  PB_PickUpList pl ON pm.Id=pl.ParentId";
            var res = Db.GetListBySqlAsync<PB_DeliveryPlanValue3DTO>(strSQL).Result.FirstOrDefault();
            if (res != null)
            {
                res.value = (total.value - res.value) <= 0 ? 0 : (total.value - res.value);
                //四舍五入保留两位小数
                res.value = Convert.ToDouble(res.value.ToString("#0.##"));
                return res;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }
        /// <summary>
        /// 大屏显示当日已备货数量
        /// </summary>
        /// <returns></returns>
        public PB_DeliveryPlanValue3DTO GetTodayPickUpAmount(int toTodayDays = 0)
        {
            string strSQL = @"SELECT IFNULL(SUM(pl.ReadyNums),0) AS value	FROM PB_DeliveryPlanDetails dd
             JOIN  PB_DeliveryPlan dp ON dp.States IN (1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays + @" AND dd.ParentId=dp.Id
             JOIN  PB_DesignList  dl ON dd.ProjectId=dl.ProjectId AND dd.BuildingId=dl.BuildingNumberID
             JOIN  PB_DeliveryNoticeItem dni ON dl.Id=dni.DesignId
             JOIN  PB_PickUpMain pm ON pm.ParentId=dni.Id AND pm.`Status`=3
             JOIN  PB_PickUpList pl ON pm.Id=pl.ParentId";
            var res = Db.GetListBySqlAsync<PB_DeliveryPlanValue3DTO>(strSQL).Result.FirstOrDefault();
            if (res != null)
            {
                //四舍五入保留两位小数
                res.value = Convert.ToDouble(res.value.ToString("#0.##"));
                return res;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }
        /// <summary>
        /// 大屏显示当日已发货数量
        /// </summary>
        /// <returns></returns>
        public PB_DeliveryPlanValue3DTO GetTodayDeliveryAmount(int toTodayDays = 0)
        {
            string strSQL = @"SELECT IFNULL(SUM(td.PackNums),0) AS value FROM PB_TransportBill tb 
JOIN PB_TransportBillDetail td ON td.ParentId = tb.Id AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())=" + toTodayDays;
            var res = Db.GetListBySqlAsync<PB_DeliveryPlanValue3DTO>(strSQL).Result.FirstOrDefault();
            if (res != null)
            {
                //四舍五入保留两位小数
                res.value = Convert.ToDouble(res.value.ToString("#0.##"));
            }
            return res;
        }
        /// <summary>
        /// 大屏显示累计发货重量
        /// </summary>
        /// <returns>{value": 43.12}</returns>
        public PB_DeliveryPlanProgressDTO GetTodayTotalWeightAsync(int toTodayDays = 0)
        {
            string strSQL = @"SELECT IFNULL(SUM(NetloadNumber),0) AS value FROM
PB_TransportBill WHERE BillType = 'Delivery' AND TO_DAYS(DepartureTime)-TO_DAYS(NOW())<=0";
            var res = Db.GetListBySqlAsync<PB_DeliveryPlanProgressDTO>(strSQL).Result.FirstOrDefault();

            if (res != null)
            {
                //四舍五入保留两位小数
                res.value = Convert.ToDecimal(res.value.ToString("#0.##"));
                res.data = res.value;
            }
            return res;
        }
        /// <summary>
        /// 大屏显示计划列表
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_DeliveryPlanDetails2DTO>> GetDeliveryPlanList()
        {
            string strSQL = @"SELECT a.ProjectName,a.ShipmentQuantity,a.TrainNumber,IFNULL(b.PackNums,0) AS PackNums FROM
(SELECT dd.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(dd.ShipmentQuantity),0) AS ShipmentQuantity,CAST(IFNULL(SUM(dd.TrainNumber),0) AS UNSIGNED) AS TrainNumber
FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id
JOIN PB_Project p ON p.Id=dd.ProjectId
GROUP BY dd.ProjectId) a
LEFT JOIN
(SELECT dd.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(td.PackNums),0) AS PackNums
FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id
JOIN PB_Project p ON p.Id=dd.ProjectId
JOIN PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID
JOIN PB_DesignDetail dt ON dt.ParentId=dl.Id
JOIN PB_TransportBillDetail td ON td.DesignDetailId=dt.Id
JOIN PB_TransportBill tb ON td.ParentId=tb.Id AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0
GROUP BY dd.ProjectId) b
ON a.ProjectId=b.ProjectId";
            var res = await Db.GetListBySqlAsync<PB_DeliveryPlanDetails2DTO>(strSQL);
            if (res.Count > 0)
            {
                res.ForEach(aData =>
                {
                    if (aData.PackNums == 0)
                    {
                        aData.StateName = "未发货";
                    }
                    else if (aData.PackNums > 0 && aData.PackNums < aData.ShipmentQuantity)
                    {
                        aData.StateName = "发货中";
                    }
                    else if (aData.PackNums >= aData.ShipmentQuantity)
                    {
                        aData.StateName = "已发货";
                    }
                    //保留两位小数
                    aData.ShipmentQuantity = Convert.ToDouble(aData.ShipmentQuantity.ToString("#0.##"));
                    aData.PackNums = Convert.ToDouble(aData.PackNums.ToString("#0.##"));
                });
            }
            return res;
        }
        /// <summary>
        /// 大屏显示计划列表2
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_DeliveryPlanDetails2DTO>> GetDeliveryPlanList2()
        {
            string strSQL = @"SELECT dd.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(dd.ShipmentQuantity),0) AS ShipmentQuantity,CAST(IFNULL(SUM(dd.TrainNumber),0) AS UNSIGNED) AS TrainNumber
FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id
JOIN PB_Project p ON p.Id=dd.ProjectId
GROUP BY dd.ProjectId";
            var res1 = await Db.GetListBySqlAsync<PB_DeliveryPlanDetails2DTO>(strSQL);
            //            strSQL = @"SELECT dd.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(td.PackNums),0) AS PackNums
            //FROM PB_DeliveryPlanDetails dd
            //JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id
            //JOIN PB_Project p ON p.Id=dd.ProjectId
            //JOIN PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID
            //JOIN PB_DesignDetail dt ON dt.ParentId=dl.Id
            //JOIN PB_TransportBillDetail td ON td.DesignDetailId=dt.Id
            //JOIN PB_TransportBill tb ON td.ParentId=tb.Id AND tb.BillType = 'Delivery' AND TO_DAYS(tb.DepartureTime)-TO_DAYS(NOW())<=0
            //GROUP BY dd.ProjectId";
            strSQL = $@"select dt.ProjectId,dt.ProjectName,IFNULL(SUM(td.PackNums),0)  AS PackNums from (
select * from PB_TransportBill where BillType = 'Delivery' AND TO_DAYS(DepartureTime)-TO_DAYS(NOW())<=0
) as tb 
JOIN PB_TransportBillDetail td ON tb.Id=td.ParentId
join pb_transportbillgroup as tg on tb.Id=tg.ParentId
join pb_deliverynotice as dt on tg.DeliveryNoticeId=dt.Id
GROUP BY dt.ProjectId,dt.ProjectName";
            var res2 = await Db.GetListBySqlAsync<PB_DeliveryPlanDetails2DTO>(strSQL);
            foreach (var item1 in res1)
            {
                bool hasPackNums = false;
                foreach (var item2 in res2)
                {
                    if (item1.ProjectId == item2.ProjectId)
                    {
                        item1.PackNums = item2.PackNums;
                        hasPackNums = true;
                    }
                }
                if (!hasPackNums)
                {
                    item1.PackNums = 0;
                }
            }
            if (res1.Count > 0)
            {
                res1.ForEach(aData =>
                {
                    if (aData.PackNums == 0)
                    {
                        aData.StateName = "未发货";
                    }
                    else if (aData.PackNums > 0 && aData.PackNums < aData.ShipmentQuantity)
                    {
                        aData.StateName = "发货中";
                    }
                    else if (aData.PackNums >= aData.ShipmentQuantity)
                    {
                        aData.StateName = "已发货";
                    }
                    //保留两位小数
                    aData.ShipmentQuantity = Convert.ToDouble(aData.ShipmentQuantity.ToString("#0.##"));
                    aData.PackNums = Convert.ToDouble(aData.PackNums.ToString("#0.##"));
                });
            }
            return res1;
        }
        /// <summary>
        /// 大屏显示发货超时
        /// </summary>
        /// <param name="toTodayDays">到今天的天数。-1表示昨天</param>
        /// <returns>超时项目列表</returns>
        public async Task<List<PB_DeliveryPlanDelayNameValueDTO>> GetDeliveryPlanDelayList(int toTodayDays = 0)
        {
            //0 => " + toTodayDays + @"          
            string strSQL = @"SELECT a.ProjectName,IF(b.DepartureTime IS NULL OR (a.Closingdate>=b.DepartureTime AND a.ShipmentQuantity>b.PackNums),TIMESTAMPDIFF(DAY,a.Closingdate,DATE_SUB(NOW(),INTERVAL -" + toTodayDays + @" DAY)),TIMESTAMPDIFF(DAY,a.Closingdate,b.DepartureTime)) AS DelayDays, IF(b.DepartureTime IS NULL OR (a.Closingdate>=b.DepartureTime AND a.ShipmentQuantity>b.PackNums),MOD(TIMESTAMPDIFF(HOUR,a.Closingdate,DATE_SUB(NOW(),INTERVAL -" + toTodayDays + @" DAY)),24),MOD(TIMESTAMPDIFF(HOUR,a.Closingdate,b.DepartureTime),24)) AS DelayHours,IF(b.DepartureTime IS NULL OR (a.Closingdate>=b.DepartureTime AND a.ShipmentQuantity>b.PackNums),MOD(TIMESTAMPDIFF(MINUTE,a.Closingdate,DATE_SUB(NOW(),INTERVAL -" + toTodayDays + @" DAY)),60),MOD(TIMESTAMPDIFF(MINUTE,a.Closingdate,b.DepartureTime),60)) AS DelayMinutes FROM
-- dp.Closingdate 转化为日期最后一秒的时间
(SELECT dd.ProjectId,p.`Name` AS ProjectName,IFNULL(SUM(dd.ShipmentQuantity),0) AS ShipmentQuantity,MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(dp.Closingdate,'%Y-%m-%d'),' 23:59:59'),'%Y-%m-%d %H:%i:%s')) AS Closingdate
FROM PB_DeliveryPlanDetails dd
JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND dd.ParentId = dp.Id
JOIN PB_Project p ON p.Id=dd.ProjectId
GROUP BY dd.ProjectId) a
LEFT JOIN
(SELECT dd.ProjectId,IFNULL(p.`Name`, '未知') AS ProjectName, MAX(tb.DepartureTime) AS DepartureTime,SUM(tb.PackNums) AS PackNums FROM PB_DeliveryPlan dp
            -- 只统计经过审核的计划
            JOIN PB_DeliveryPlanDetails dd ON dd.ParentId = dp.Id AND dp.States IN(1, 2)
            JOIN PB_DesignList dl ON dd.ProjectId = dl.ProjectId AND dd.BuildingId = dl.BuildingNumberID
            JOIN PB_DesignDetail dt ON dt.ParentId=dl.Id
            JOIN PB_Project p ON p.Id = dd.ProjectId
            -- 有离场时间的才统计重量和最迟离场时间, 相同设计明细的派车单汇总
            JOIN (SELECT MAX(bl.DepartureTime) AS DepartureTime, SUM(bd.PackNums) AS PackNums, bd.DesignDetailId FROM PB_TransportBill bl 
						JOIN PB_TransportBillDetail bd ON bl.Id=bd.ParentId AND bl.BillType = 'Delivery' AND bl.DepartureTime IS NOT NULL
						GROUP BY bd.DesignDetailId) tb
		   ON tb.DesignDetailId=dt.Id
            -- 按照项目汇总
            GROUP BY dd.ProjectId) b	
ON a.ProjectId=b.ProjectId
            -- 1.时间延后 2.时间未延后但没发足计划数量 3.离场时间为空且计划时间早于当前
WHERE((a.Closingdate < b.DepartureTime) OR(a.Closingdate<DATE_SUB(NOW(), INTERVAL -" + toTodayDays + @" DAY) AND a.Closingdate >= b.DepartureTime AND a.ShipmentQuantity > b.PackNums) OR (b.DepartureTime IS NULL AND a.Closingdate < DATE_SUB(NOW(), INTERVAL -" + toTodayDays + @" DAY)))
ORDER BY DelayDays DESC, DelayHours DESC,DelayMinutes DESC";

            var list = await Db.GetListBySqlAsync<PB_DeliveryPlanDelayDTO>(strSQL);
            List<PB_DeliveryPlanDelayNameValueDTO> res = new List<PB_DeliveryPlanDelayNameValueDTO>();
            if (list.Count > 0)
            {

                list.ForEach(aData =>
                {
                    PB_DeliveryPlanDelayNameValueDTO item = new PB_DeliveryPlanDelayNameValueDTO();
                    item.name = aData.ProjectName + aData.BuildingName;
                    item.value = (aData.DelayDays == 0 ? string.Empty : aData.DelayDays + "日") + (aData.DelayHours == 0 ? string.Empty : aData.DelayHours + "时") + (aData.DelayMinutes == 0 ? string.Empty : aData.DelayMinutes + "分");
                    res.Add(item);
                });
            }
            return res;
        }
        /// <summary>
        /// 大屏显示7日发货城市
        /// </summary>
        /// <param name="toTodayDays">到今天的天数</param>
        /// <returns>['新乡市',[[{name: '新乡市'}, {name: '新乡市', value: 200, dataInfo: 发货总数:2000件 }], [{name: '新乡市'}, {name: '呼和浩特市', value: 100, dataInfo: 发货数:1000件 }], [{name: '新乡市'}, {name: '成都市', value: 100, dataInfo: 发货数:1000件 }]]]</returns>
        public async Task<List<List<object>>> GetDeliveryPlanCity(int toTodayDays = 0)
        {
            PB_DeliveryPlanCityDTO res = new PB_DeliveryPlanCityDTO();
            //实际发货总量
            //         string strSQL = @"SELECT IFNULL(SUM(td.PackNums),0) AS value FROM PB_TransportBillDetail td		

            //JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(tb.DepartureTime)<=" + (7 + toTodayDays).ToString() + @"			
            //         JOIN PB_DesignDetail dt ON td.DesignDetailId=dt.Id
            //         JOIN PB_DesignList dl ON dt.ParentId=dl.Id
            //JOIN PB_Project p ON dl.ProjectId=p.Id
            //         JOIN Base_Region r ON p.CityId = r.Id";
            string strSQL = $@"	select IFNULL(SUM(td.PackNums),0) AS value  from (
	select * from pb_transportbill where BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(DepartureTime)<= (7+{toTodayDays}) )as tb 	join PB_TransportBillDetail as td on tb.Id=td.ParentId";
            //计划发货总量
            //     string strSQL = @"SELECT IFNULL(SUM(dd.ShipmentQuantity),0) AS value	FROM PB_DeliveryPlanDetails dd
            //JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW())=" + toTodayDays + @" AND dd.ParentId = dp.Id";
            double total = Db.GetListBySqlAsync<PB_DeliveryPlanValue3DTO>(strSQL).Result.FirstOrDefault().value;
            //各市实际发货量

            //         strSQL = @"SELECT r.`Name` AS name,IFNULL(SUM(td.PackNums),0) AS dataInfo FROM PB_TransportBillDetail td		
            //JOIN PB_TransportBill tb ON tb.Id=td.ParentId AND tb.BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(tb.DepartureTime)<=" + (7 + toTodayDays).ToString() + @"
            //JOIN PB_DesignDetail dt ON td.DesignDetailId=dt.Id
            //         JOIN PB_DesignList dl ON dt.ParentId=dl.Id
            //JOIN PB_Project p ON dl.ProjectId=p.Id
            //         JOIN Base_Region r ON p.CityId = r.Id
            //         GROUP BY p.CityId";
            strSQL = $@"select r.`Name` AS name,IFNULL(SUM(td.PackNums),0) AS dataInfo  from (
	select * from pb_transportbill where BillType='Delivery' AND TO_DAYS(NOW())-TO_DAYS(DepartureTime)<= (7+{toTodayDays}) )as tb 	
	join PB_TransportBillDetail as td on tb.Id=td.ParentId
	JOIN pb_transportbillgroup dt ON tb.Id=dt.ParentId
	join pb_deliverynotice as tice on dt.DeliveryNoticeId=tice.Id
	join PB_Project as p on tice.ProjectId=p.Id
	JOIN Base_Region r ON p.CityId = r.Id
            GROUP BY p.CityId";
            //各市计划发货量
            //strSQL = @"SELECT r.`Name` AS name, IFNULL(SUM(dd.ShipmentQuantity), 0) AS dataInfo FROM PB_DeliveryPlanDetails dd
            //  JOIN PB_DeliveryPlan dp ON dp.States IN(1,2) AND TO_DAYS(dp.Closingdate)-TO_DAYS(NOW()) = " + toTodayDays + @" AND dd.ParentId = dp.Id
            //  JOIN PB_Project p ON dd.ProjectId = p.Id
            //  JOIN Base_Region r ON p.CityId = r.Id
            //  GROUP BY p.CityId";
            var temp = Db.GetListBySqlAsync<PB_DeliveryPlanCityReceiveCityDTO>(strSQL);
            if (temp.Result.Count > 0)
            {
                List<object> temp2 = res.lists[0][1] as List<object>;
                //.ToString("#0.##")四舍五入保留两位小数，并去掉末尾的".0"
                temp2[0] = new List<object>() { new PB_DeliveryPlanCityDeliveryCity(), new PB_DeliveryPlanCityReceiveCityDTO { name = "邯郸市", value = 200, dataInfo = "发货总数:" + total.ToString("#0.##") + "件" } };
                total = total == 0 ? 1 : total;
                temp.Result.ForEach(aData =>
                {
                    if (aData.name.Contains("市辖区"))
                    {
                        aData.name = aData.name.Substring(0, aData.name.Length - 3);
                    }
                    int val = (int)(Convert.ToDouble(aData.dataInfo) / total * 200) == 0 ? 1 : (int)(Convert.ToDouble(aData.dataInfo) / total * 200);
                    temp2.Add(new List<object>() { new PB_DeliveryPlanCityDeliveryCity(), new PB_DeliveryPlanCityReceiveCityDTO { name = aData.name, value = val, dataInfo = "发货数:" + Convert.ToDouble(aData.dataInfo).ToString("#0.##") + "件" } });
                });
                return res.lists;
            }
            else
            {
                // throw new BusException("暂无数据");
                return null;
            }
        }
        #endregion

        #region 私有成员

        #endregion
    }
}